package com.project.dao.impl;

import com.project.bean.ChannelBean;
import com.project.bean.CutPageBean;
import com.project.bean.ThemeBean;
import com.project.bean.UserBean;
import com.project.dao.IThemeDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
@SuppressWarnings("all")
@Repository
public class ThemeDaoImpl extends BaseDao implements IThemeDao {
    @Override
    public CutPageBean<ThemeBean> findByItem(int pageNO, Integer channelId, String name,
                                             String username, LocalDate startDate, LocalDate endDate) {
        CutPageBean<ThemeBean> cutPage = new CutPageBean<>();
        String sql = " where 1=1 ";
        List value = new ArrayList();
        if (channelId != null){
            sql += " and pk_channelId = ? ";
            value.add(channelId);
        }
        if (name != null && name.length() != 0) {
            sql += " and t_name like ? ";
            value.add("%" + name + "%");
        }
        if (username != null && username.length() != 0){
            sql += " and u_nickname like ? ";
            value.add("%" + username + "%");
        }
        if (startDate != null){
            sql += " and t_date >= ? ";
            value.add(startDate);
        }
        if (endDate != null){
            sql += " and t_date <= ? ";
            value.add(endDate);
        }
        this.setConnection();
        try {
            ps = con.prepareStatement("SELECT z.*,u.u_nickname FROM t_user u RIGHT join \n" +
                    "(SELECT t.*,c.pk_channelId FROM t_channel c left JOIN\n" +
                    "\t(SELECT t.*,COUNT(c.fk_themeId) comment FROM t_theme t left join t_comment c ON t.pk_themeId = c.fk_themeId GROUP BY t.pk_themeId\n" +
                    ") t ON c.pk_channelId = t.fk_channelId) z ON u.pk_userId = z.fk_userId " + sql +" GROUP BY pk_themeId LIMIT ?,?");

            for (int i = 0; i < value.size(); i++) {
                ps.setObject(i + 1, value.get(i));
            }
            ps.setObject(value.size() + 1, (pageNO - 1) * cutPage.PAGESIZE);
            ps.setObject(value.size() + 2, cutPage.PAGESIZE);

            rs = ps.executeQuery();
            while (rs.next()){
                ThemeBean theme = this.getTheme();
                theme.getUser().setNickname(rs.getString("u_nickname"));
                theme.setCommentNum(rs.getInt("comment"));

                cutPage.getList().add(theme);
            }

            ps = con.prepareStatement("SELECT COUNT(*) c FROM t_user u RIGHT join \n" +
                    "(SELECT t.*,c.pk_channelId FROM t_channel c left JOIN\n" +
                    "\t(SELECT t.*,COUNT(c.fk_themeId) comment FROM t_theme t left join t_comment c ON t.pk_themeId = c.fk_themeId GROUP BY t.pk_themeId\n" +
                    ") t ON c.pk_channelId = t.fk_channelId) z ON u.pk_userId = z.fk_userId" + sql);

            for (int i = 0; i < value.size(); i++) {
                ps.setObject(i + 1, value.get(i));
            }

            rs = ps.executeQuery();
            if (rs.next()) {
                cutPage.setCount(rs.getInt("c"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return cutPage;
    }

    @Override
    public void add(ThemeBean theme) {
        this.setConnection();
        try {
            ps = con.prepareStatement("insert into t_theme(t_name,t_content,t_date,fk_userId,fk_channelId)" +
                    "values (?,?,?,?,?)");
            ps.setObject(1,theme.getName());
            ps.setObject(2,theme.getContent());
            ps.setObject(3,theme.getDate());
            ps.setObject(4,theme.getUser().getId());
            ps.setObject(5,theme.getChannel().getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.close();
        }

    }

    @Override
    public ThemeBean findById(Integer id) {
        ThemeBean theme = null;
        this.setConnection();
        try {
            ps = con.prepareStatement("select *from t_theme where pk_themeId = ?");
            ps.setObject(1,id);

            rs = ps.executeQuery();
            if (rs.next()) {
                theme = this.getTheme();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return theme;
    }

    private ThemeBean getTheme() throws SQLException {
        ThemeBean theme = new ThemeBean();
        theme.setId(rs.getInt("pk_themeId"));
        theme.setName(rs.getString("t_name"));
        theme.setContent(rs.getString("t_content"));
        theme.setDate(LocalDate.parse(rs.getString("t_date")));
        UserBean user = new UserBean();
        user.setId(rs.getInt("fk_userId"));
        theme.setUser(user);
        ChannelBean channel = new ChannelBean();
        channel.setId(rs.getInt("fk_channelId"));
        theme.setChannel(channel);

        return theme;
    }
}
